package src.DAO;

import src.Entities.Afiliado;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import src.jdbc.PostgreSQL;
import src.Entities.Programa;
import src.Entities.Idioma;

/**
 *
 * @author Felipe
 */
public class AfiliadoDAO
{

    /** public String deleteAfiliado(int afiliadoId)
    {
    try
    {
    String sql = "DELETE FROM parentesco WHERE parentesco_id = ?;";
    PreparedStatement delete = PostgreSQL.getConnection().prepareStatement(sql);
    delete.setInt(1, parentescoId);
    return String.valueOf(delete.execute());
    }
    catch (Exception e)
    {
    return e.toString();
    }
    }*/
    public String insertAfiliado(Afiliado afiliado)
    {
        try
        {
            String msg;
            String sql = "INSERT " +
                    "       INTO persona(persona_id,genero_id, nombre, app_paterno, app_materno, " +
                    "       run, dv, fecha_nacimiento, estado_civil,nacionalidad)" +
                    "      VALUES (?, ?, ?, ?, ?,?,?,?,?,?)";
            PreparedStatement insert = PostgreSQL.getConnection().prepareStatement(sql);
            insert.setInt(1, afiliado.getIdPersona());
            insert.setInt(2, afiliado.getIdGenero());
            insert.setString(3, afiliado.getNombre());
            insert.setString(4, afiliado.getApellidoPat());
            insert.setString(5, afiliado.getApellidoMat());
            insert.setString(6, afiliado.getRun());
            insert.setString(7, String.valueOf(afiliado.getDv()));
            java.sql.Date fechaNac = new java.sql.Date(afiliado.getFechaNac().getTime());
            insert.setDate(8, fechaNac);
            insert.setString(9, afiliado.getEstadoCivil());
            insert.setString(10, afiliado.getNacionalidad());
            msg = String.valueOf(insert.execute());


            // INSERTA USUARIO
            String sql1 = "INSERT " +
                    "       INTO usuario(usuario_id,persona_id,pass, mail,es_admin)" +
                    "      VALUES (NEXTVAL('usuario_seq'), ?, ?, ?, ?)";
            PreparedStatement insert1 = PostgreSQL.getConnection().prepareStatement(sql1);
            insert1.setInt(1, afiliado.getIdPersona());
            insert1.setString(2, afiliado.getPass());
            insert1.setString(3, afiliado.getEmail());
            insert1.setString(4, String.valueOf('N'));
            msg = String.valueOf(insert.execute());

            // INSERTA RELACION PROGRAMA PERSONA
            for (Programa pr : afiliado.getPrograma())
            {
                String sql2 = "INSERT " +
                        "       INTO persona_programa(pers_prog_id,programa_id,persona_id)" +
                        "      VALUES (NEXTVAL('persona_programa_seq'), ?, ?)";
                PreparedStatement insert2 = PostgreSQL.getConnection().prepareStatement(sql2);
                insert2.setInt(1, pr.getProgramaId());
                insert2.setInt(2, afiliado.getIdPersona());
                msg = String.valueOf(insert.execute());
            }

            // INSERTA  RELACION IDIOMA PERSONA
            for (Idioma id : afiliado.getIdioma())
            {
                String sql2 = "INSERT " +
                        "       INTO persona_idioma(pers_idioma_id,idioma_id,persona_id,skill_id)" +
                        "      VALUES (NEXTVAL('persona_idioma_seq'), ?, ?,?)";
                PreparedStatement insert2 = PostgreSQL.getConnection().prepareStatement(sql2);
                insert2.setInt(1, id.getIdiomaId());
                insert2.setInt(2, afiliado.getIdPersona());
                insert2.setInt(3, id.getSkillId());
                msg = String.valueOf(insert.execute());
            }

            return msg;

        }
        catch (Exception e)
        {
            return e.toString();
        }
    }

    public int buscaIdPersona()
    {
        int id = 0;
        try
        {
            Connection con = PostgreSQL.getConnection();
            Statement select = con.createStatement();
            String sql3 = "SELECT NEXTVAL('persona_seq')";
            ResultSet query = select.executeQuery(sql3);
            while (query.next())
            {
                id = query.getInt("nextval");
            }
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
        return id;
    }

    public ArrayList selectAfiliado(String sql)
    {
        ArrayList results = new ArrayList();
        try
        {
            Connection con = PostgreSQL.getConnection();
            Statement select = con.createStatement();
            ResultSet query = select.executeQuery(sql);


            while (query.next())
            {
                Afiliado afiliado = new Afiliado();
                afiliado.setIdPersona(query.getInt("idPersona"));
                afiliado.setNombre(query.getString("nombre"));
                afiliado.setApellidoPat(query.getString("app_paterno"));
                afiliado.setApellidoMat(query.getString("app_materno"));
                afiliado.setRun(query.getString("run"));
                afiliado.setDv(query.getString("dv").charAt(0));
                afiliado.setEmail(query.getString("email"));
                results.add(afiliado);
            }
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
        return results;
    }
}
